home *** CD-ROM | disk | FTP | other *** search
/ Computerized Investing - Spreadsheet Collection / Spreadsheet Collection.iso / pc / ibm13 / djia.doc next >
Encoding:
Text File  |  1991-07-15  |  7.9 KB  |  179 lines
  1. A Spreadsheet Using Some Historical Market Data
  2. By Fred Shipley, Ph.D.
  3. Computerized Investing, July/August 1989
  4.  
  5.       In this spreadsheet we use data for the Dow Jones Industrials
  6. to understand certain fundamental market relationships.  With the
  7. data, in columns A through F, you can create some formulas and
  8. observe a number of characteristics of the Dow Jones Average. 
  9. These long term historical characteristics provide a means of
  10. judging the market's current position.
  11.  
  12.       This spreadsheet uses some information about the Dow Jones
  13. Industrials that appears regularly in Barron's.  Although this
  14. table is printed with some regularity, there is no set schedule,
  15. so you will have to watch for it to appear.  Since most of the data
  16. is annual, there is no need to perform frequent updating.  Data for
  17. the previous calendar year is usually available by May.
  18.  
  19.       The basic data is reproduces in columns A through F and
  20. examples of possible calculations are given in columns G through
  21. M.  For comparative purposes, some historical averages and standard
  22. deviations are calculated near the bottom of the worksheet in rows
  23. 71 and 72. 
  24.  
  25.       In the spreadsheet, the return on equity (column G) is
  26. current earnings per share divided by book value.
  27.  
  28.  
  29.                     Earnings Per Share                (column D)
  30.             ROE = --------------------
  31.                         Book Value                    (column C)
  32.  
  33.  
  34.       The price-earnings ratio (column H) is a trailing ratio
  35. because it takes the index value divided by the last 12 months'
  36. earnings per share.  For comparative purposes, we have also
  37. computed a normalized P/E (column I), which is the current index
  38. value divided by the following year's expected earnings.
  39.  
  40.                          Closing Price          (column B)
  41.       Trailing P/E = --------------------
  42.                        Earnings Per Share             (column D)
  43.       
  44.                            Closing Price              (column B)
  45.       Normalized P/E = --------------------
  46.                          Earnings Per Share     (column D)
  47.                           (Following Year)
  48.  
  49.  
  50.       In some years, the calculated price-earnings ratios could be
  51. either unusually large or negative.  For example, in 1932, the
  52. depths of the Depression, total earnings for the industrials were:
  53. $0.51.
  54. Using this figure would result in a trailing P/E of -117.5:
  55.  
  56.                         59.93
  57.                         -----_  =  -117.5
  58.                        -$0.51                               
  59.  
  60.       Clearly, such a number is not meaningful, and negative P/Es
  61. are reported as such in the financial media.  As another example,
  62. in 1982 earnings were $9.15 and the Dow closed at 1046.54, which
  63. gives a trailing P/E of 114.4.  Again, this P/E is an aberration-
  64. -what a statistician would call an outlier.
  65.  
  66.       The problem for an investor is what to do about these
  67. numbers. If you were to calculate an average P/E, the average would
  68. be distorted by including these outliers.  Since you are interested
  69. in learning something about long-run performance, determining an
  70. average value and the variation around that value is critical.  As
  71. a reasonable rule of thumb, we will simply exclude for any
  72. calculation P/E ratios that are negative or greater than 30. 
  73. Essentially this excludes the aberrations caused by the Depression
  74. (1932 and 1933) and the more recent sharp recession in 1982.
  75.  
  76.       To exclude these numbers, we use the database average
  77. function, @DAVG.  This function allows us to determine an average
  78. of those values that satisfy the conditions, or criteria, we
  79. specify.  The form of the function is:
  80.  
  81.             @DAVG(input range, offset, criterion range)
  82.  
  83. where:  input range is the area of the spreadsheet where
  84.         the data is located, for trailing P/E, cells H7 to H67;
  85.  
  86.         offset counts the number of columns from the beginning of
  87.         the database, starting with zero. Since we have only one
  88.         column of data, the offset in this case is zero.
  89.  
  90.         criterion range is the area of the spreadsheet in which we
  91.         specify our conditions.  In this case, we have our criteria
  92.         immediately under the data we are analyzing, in cells H69
  93.         and H70 for trailing P/E.
  94.  
  95. For our trailing price-earnings ratios, the formula is:
  96.  
  97.             @DAVG(H7..H67,0,H69..H70)
  98.  
  99.       Certain quirks of 1-2-3 must be recognized when using this
  100. database function.  First, the input range must include the
  101. identifier of the data field we are analyzing.  Second, the
  102. function does not work correctly of there is a blank cell or label
  103. between the field name and the first item of data.  (Don't ask ne
  104. why; I have no idea! It's silly and often causes trouble.)  This
  105. second reason is why we have an entry in the row for the year 1928,
  106. even though there is no data for that year other than the closing
  107. value of the Dow.  Third, it makes a difference whether you use
  108. absolute or relative addresses in the criterion.  All criteria
  109. should be written with relative addresses.
  110.  
  111.       Finally, we have to specify our criteria.  There are a number
  112. of ways to do this.  We will make a single formula, using the
  113. logical AND.  For example, the average of P/Es that are positive
  114. and less than or equal to 30 in our criterion would be
  115. +H8>0#AND#+H8<=30.  For other ways to set this up, check one of the
  116. many Lotus books, such as Que's "Using 1-2-3." 
  117.  
  118.       The dividend payout ratio (column J) is the percentage of
  119. earnings actually paid out in dividends.  It is defined as
  120. dividends per share divided by earnings per share.
  121.  
  122.       Dividend          Dividends Per Share     (column E)
  123.       Payout      =     -------------------
  124.       Ratio                   Earnings Per Share      (column D)
  125.  
  126.       The earnings retention ratio (column K) is the percentage of
  127. earnings that are reinvested by the companies in the Dow Jones
  128. index.  It is usually thought of as one minus the dividend payout
  129. ratio.
  130.  
  131.       Earnings          Earnings Retained Per Share
  132.       Retention   =     ---------------------------
  133.       Ratio                         Earnings Per Share
  134.  
  135.                   =     1  -  Dividend Payout Ratio
  136.  
  137.                   =     1  -  Column J
  138.  
  139.       The dividend yield (column L) is the dividend (current cash
  140. flow) return as a percentage of the year-end value of the Dow Jones
  141. index.
  142.  
  143.  
  144.  
  145.                               Dividends Per Share      (column E)
  146.       Div. Yield        =     -------------------
  147.                               Year-End Value (DJIA)    (column B)
  148.  
  149.       Total return (column M) is the change in price plus dividends
  150. per share divided by the closing price of the previous year.
  151.  
  152.                         (DJIA1 - DJIA0) + Dividend1
  153.       Total Return   =        ---------------------------
  154.                                     DJIA0
  155.  
  156. Where      DJIA1 is this year's price               (column B)
  157.            DJIA0 is the previous year's price       (column B)
  158.            Dividend1 is the dividend
  159.                    received during the year         (column E)
  160.  
  161.       The change in CPI in column F is the annual percentage change
  162. in the Consumer Price Index.  This data is taken from the Federal
  163. Reserve Bulletin and is reported regularly in the media.
  164.  
  165.       As you would expect, there is an inverse relationship between
  166. the rate of inflation and the price-earnings ratios.  When
  167. inflation increases, so does investors' requited rate of return. 
  168. This increase in the required rate of return means that investors
  169. are willing to pay less for a given level of earnings, since those
  170. earnings are inflated.  The result is a lower P/E.
  171.  
  172.       There are obviously many other such comparisons you can make.
  173.  
  174. Examining the dividend yield on a regular basis, for example, would
  175. have indicated that a historically low yield prevailed in August,
  176. 1987.  Other possibilities are limited by your imagination.
  177.  
  178. (c) Copyright 1989 by the
  179. American Association of Individual Investors